﻿Imports System.Data
Imports System.Data.SqlClient
Public Class AgendamentoDAL
    Public Sub New()
        'contrutor padrão
    End Sub
    Dim cmd As SqlCommand
    Dim objD As Dados
#Region "Métodos"
    Public Function inserir(ByVal age_data As Date, ByVal age_data_previsao As Date,
                            ByVal age_data_entrega As Nullable(Of Date), cod_cli_destinatario As Integer,
                            cod_cli_remetente As Integer) As Integer
        Dim codigo As Integer
        Try
            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "insert into agendamento_carga(age_data,age_data_previsao,age_data_entrega) values " & _
                "(@age_dt,@age_dt_previsao,@age_dt_entrega)"
            cmd.Parameters.Add(New SqlParameter("@age_dt", SqlDbType.Date)).Value = age_data
            cmd.Parameters.Add(New SqlParameter("@age_dt_previsao", SqlDbType.Date)).Value = age_data_previsao
            If (age_data_entrega.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@age_dt_entrega", SqlDbType.Date)).Value = age_data_entrega
            Else
                cmd.Parameters.Add(New SqlParameter("@age_dt_entrega", SqlDbType.Date)).Value = DBNull.Value
            End If
            codigo = objD.executacomandoAI(cmd, "agendamento_carga")
            cmd = New SqlCommand()
            cmd.CommandText = "insert into clientes_do_agendamento(age_codigo,pes_codigo,pes_tipo_dr) " & _
                "values(@cod_agendamento,@cod_destinatario,'D')"
            cmd.Parameters.Add(New SqlParameter("@cod_agendamento", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@cod_destinatario", SqlDbType.Int)).Value = cod_cli_destinatario
            objD.executacomando(cmd)
            cmd = New SqlCommand()
            cmd.CommandText = "insert into clientes_do_agendamento(age_codigo,pes_codigo,pes_tipo_dr) " & _
                "values(@cod_agendamento,@cod_remetente,'R')"
            cmd.Parameters.Add(New SqlParameter("@cod_agendamento", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@cod_remetente", SqlDbType.Int)).Value = cod_cli_remetente
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return codigo
    End Function
    Public Sub alterar(ByVal age_codigo As Integer, ByVal age_data As Date,
                       ByVal age_data_previsao As Date, ByVal age_data_entrega As Nullable(Of Date), ByVal cod_cli_destinatario As Integer,
                       ByVal cod_cli_remetente As Integer)
        Try
            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "update agendamento_carga set " & _
                "age_data = @age_dt,age_data_previsao = @age_dt_previsao,age_data_entrega = @age_dt_entrega where age_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@age_dt", SqlDbType.Date)).Value = age_data
            cmd.Parameters.Add(New SqlParameter("@age_dt_previsao", SqlDbType.Date)).Value = age_data_previsao
            If (age_data_entrega.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@age_dt_entrega", SqlDbType.Date)).Value = age_data_entrega
            Else
                cmd.Parameters.Add(New SqlParameter("@age_dt_entrega", SqlDbType.Date)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = age_codigo
            objD.executacomando(cmd)
            cmd = New SqlCommand
            cmd.CommandText = "update clientes_do_agendamento set pes_codigo = @cod_destinatario where age_codigo = @cod_agendamento and " & _
                "pes_tipo_dr = 'D'"
            cmd.Parameters.Add(New SqlParameter("@cod_agendamento", SqlDbType.Int)).Value = age_codigo
            cmd.Parameters.Add(New SqlParameter("@cod_destinatario", SqlDbType.Int)).Value = cod_cli_destinatario
            objD.executacomando(cmd)
            cmd = New SqlCommand
            cmd.CommandText = "update clientes_do_agendamento set pes_codigo = @cod_remetente where age_codigo = @cod_agendamento and " & _
                "pes_tipo_dr = 'R'"
            cmd.Parameters.Add(New SqlParameter("@cod_agendamento", SqlDbType.Int)).Value = age_codigo
            cmd.Parameters.Add(New SqlParameter("@cod_remetente", SqlDbType.Int)).Value = cod_cli_remetente
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub
    Public Sub excluir(age_codigo As Integer)
        Try
            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "delete from clientes_do_agendamento where age_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = age_codigo
            objD.executacomando(cmd)
            cmd = New SqlCommand
            cmd.CommandText = "delete from agendamento_carga where age_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = age_codigo
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub
    Function localizar(ByVal descricao As String) As DataTable
        Dim tab As DataTable = Nothing
        Try
            cmd = New SqlCommand
            objD = New Dados
            If (descricao = "ultimo") Then
                cmd.CommandText = "SELECT age_codigo = max(age_codigo) FROM agendamento_carga "
            ElseIf (descricao = "primeiro") Then
                cmd.CommandText = "SELECT age_codigo = min(age_codigo) FROM agendamento_carga "
            Else
                'Comando para localizar vários registros no banco
                cmd.CommandText = "SELECT vw_cargas_agendamento where age_data between @dt_inicio and @dt_final"
                'cmd.Parameters.Add(New SqlParameter("@dt_inicio", SqlDbType.Date)).Value = dt_inicio
                'cmd.Parameters.Add(New SqlParameter("@dt_final", SqlDbType.Date)).Value = dt_final
            End If
            'Execução do comando
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function
    Public Function localizar(age_codigo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "select * from agendamento_carga where age_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = age_codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function
    Function localizarProxAnterior(ByVal descricao As String, ByVal codigo As Integer) As DataTable
        Dim tab As DataTable = Nothing
        Try
            cmd = New SqlCommand
            objD = New Dados
            If (descricao = "proximo") Then
                cmd.CommandText = "SELECT age_codigo FROM agendamento_carga WHERE age_codigo = (SELECT MIN(age_codigo) FROM " +
                                    "agendamento_carga WHERE age_codigo > @codigo)"
            ElseIf (descricao = "anterior") Then
                cmd.CommandText = "SELECT age_codigo FROM agendamento_carga  WHERE age_codigo = (SELECT MAX(age_codigo) FROM " +
                                    "agendamento_carga WHERE age_codigo < @codigo)"
            End If
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function
    Public Function localizar(nome As String, tipo As Char) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "SELECT a.age_codigo,ca.pes_codigo,p.pes_nome,ca.pes_tipo_dr,a.age_data " & _
                                "FROM agendamento_carga a " & _
                                "INNER JOIN clientes_do_agendamento ca ON ca.age_codigo = a.age_codigo " & _
                                "INNER JOIN pessoa p ON p.pes_codigo = ca.pes_codigo " & _
                                "WHERE ca.pes_tipo_dr = @tipo and p.pes_nome like @nome"
            cmd.Parameters.Add(New SqlParameter("@tipo", SqlDbType.Char)).Value = tipo
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = "%" & nome & "%"
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function
    Public Function localizarCargasAgendamento(ByVal age_codigo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "select * from vw_cargas_agendamento where age_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = age_codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        objD = Nothing
        cmd = Nothing
        Return tab
    End Function
    Public Function validaCarga(ByVal protocolo As String) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "select * from vw_cargas_agendadas where car_protocolo = @protocolo"
            cmd.Parameters.Add(New SqlParameter("@protocolo", SqlDbType.VarChar)).Value = protocolo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function
    Public Function verificaStatus(codigo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "select * from cargas where car_status <> 0 and car_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function
    Function VerificaExclusao(ByVal codigo As Integer) As Boolean
        Dim verifica As Boolean = False
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "Select age_codigo from cargas_da_lotacao where age_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            verifica = objD.executacomandoString(cmd, "cargas_da_lotacao")
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return verifica
    End Function
    Public Function localizarTipoCarga(codigo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "select c.car_codigo,tc.tc_descricao from cargas c,tipos_cargas tc " & _
                "where c.tc_codigo = tc.tc_codigo and c.car_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function
    Public Function localizarClientesAgendamento(ByVal age_codigo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "select pes_codigo,pes_tipo_dr from clientes_do_agendamento where age_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = age_codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function
    Public Function verificaLotacao(age_codigo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "select age_codigo from cargas_da_lotacao where age_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = age_codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        objD = Nothing
        cmd = Nothing
        Return tab
    End Function
#End Region
End Class
